/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ebdesk.controller.dhkp;
import java.sql.*;
import java.util.*;
import java.io.*;

/**
 *
 * @author mbenk
 */
public class DHKP {
    
    public Connection dbCon(){
        Connection con = null;
        try{
            Properties p = new Properties();
            FileInputStream in = new FileInputStream("config.properties");
            p.load(in);
            Class.forName(p.getProperty("driver"));
            con = DriverManager.getConnection(p.getProperty("connectionstring"),p.getProperty("username"),p.getProperty("password"));            
        }
        catch(FileNotFoundException e){
            System.err.println(e);
        }
        catch(IOException e){
            System.err.println(e);
        }
        catch(ClassNotFoundException e){
            System.err.println(e);
        }
        catch(SQLException e){
            System.err.println(e);
        }        
        finally{
            return con; 
        }
    }
    public Object[][] getData(String Query){
        Object[][] objData = null;
        try{
            Connection conn = dbCon();
            Statement s;
            s = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

            ResultSet rs;
            int row = 0;
            int column = 0;
            int rowcount;
            int paging;

            rs = s.executeQuery("select pbb.pbb_setting.value from pbb.pbb_setting where pbb.pbb_setting.setting ='paging'");
            rs.next();
            paging = rs.getInt(1); 

            rs = s.executeQuery(Query);
            rs.last();
            rowcount = rs.getRow();
            rs.beforeFirst();

            if(rowcount > paging){
                rowcount = paging;
            }
            objData = new Object[rowcount][6];                       
            while(rs.next()){
                objData[row][column++] = Lib.FormatSPPT(rs.getString(1));//NOP
                objData[row][column++] = rs.getString(2);//Nama
                objData[row][column++] = rs.getString(3);//Alamat wajib pajak
                objData[row][column++] = rs.getString(47);//Alamat objek pajak
                objData[row][column++] = Lib.NumberFormat(Double.parseDouble(rs.getString(29)));//Pajak Terhutang
                objData[row][column++] = rs.getString(26);//Tgl Terbit            
                column = 0;
                row++;
            }

            if(conn!=null){
                conn.close();
            }
        }
        catch(SQLException e){
            System.err.println(e);
        }
        finally{
            return objData;
        }
    } 
    public Object[] getDataCB(int jeniscb){
        return getDataCB(jeniscb,"");
        
    }
     public Object[] getDataCB(int jeniscb, String kecamatan) {
        Object[] dataCB = null;
        
        try{
            String query = "";
            int rowcount;

            Connection conn = dbCon();

            Statement s;
            s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

            ResultSet rs;

            switch(jeniscb){
                //cbKec
                case 1: query = "select * "
                        + "from pbb.pbb_master_kecamatan "
                        + "where substring(pbb.pbb_master_kecamatan.kode_kecamatan from 1 for 4) = (select pbb.pbb_setting.value from pbb.pbb_setting where pbb.pbb_setting.setting = 'kode_kota') "
                        + "order by pbb.pbb_master_kecamatan.kode_kecamatan";
                        break;
                //cbKel
                case 2: query = "select * "
                        + "from pbb.pbb_master_kelurahan "
                        + "where substring(pbb.pbb_master_kelurahan.kode_kelurahan from 1 for 7) = (select pbb.pbb_master_kecamatan.kode_kecamatan from pbb.pbb_master_kecamatan where "
                        + "     pbb.pbb_master_kecamatan.nama_kecamatan = '"+kecamatan+"'"
                        + "     and substring(pbb.pbb_master_kecamatan.kode_kecamatan from 1 for 4) = (select pbb.pbb_setting.value from pbb.pbb_setting where pbb.pbb_setting.setting = 'kode_kota')) "
                        + "order by pbb.pbb_master_kelurahan.kode_kelurahan";
                        break;
                //cbBuku
                case 3: query = "select pbb.pbb_sppt_buku.nama_buku from pbb.pbb_sppt_buku order by pbb.pbb_sppt_buku.nama_buku";
                        break;

            }

            rs = s.executeQuery(query);

            rs.last();
            rowcount = rs.getRow();
            rs.beforeFirst();        

            //Tambahkan string kosong ke combo box Kecamatan & Buku
            if(jeniscb!=2){
                dataCB = new Object[rowcount+1];            
                dataCB[0] = null;
                rowcount = 1;
            }else{
                dataCB = new Object[rowcount];            
                rowcount = 0;
            }

            while(rs.next()){
                switch(jeniscb){
                    case 1: dataCB[rowcount] = rs.getString(4).substring(4) +" - "+rs.getString(3).toUpperCase(); break;
                    case 2: dataCB[rowcount] = rs.getString(4).substring(7) +" - "+rs.getString(3).toUpperCase(); break;
                    case 3: dataCB[rowcount] = rs.getString(1).toUpperCase(); break;
                }
                rowcount++;
            }
            if(conn!=null){
                conn.close();
            }
        }
        catch(SQLException e){
            System.err.println(e);
        }
        finally{
            return dataCB;
        }
    } 
    public String[][] getPrintData(String query){
        String[][] objData = null;
        try{
            Connection conn = dbCon();
            Statement s;
            s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);

            ResultSet rs;
            int row = 0;
            int column = 0;
            int rowcount;


            //Hitung jumlah row dalam ResultSet
            rs = s.executeQuery(query);
            rs.last();
            rowcount = rs.getRow();
            rs.beforeFirst();

            objData = new String[rowcount][14];
            while(rs.next()){
                objData[row][column++] = rs.getString(57);//0. Kelompok buku
                objData[row][column++] = rs.getString(25);//1. Tahun
                objData[row][column++] = rs.getString(9);//2. Propinsi 
                objData[row][column++] = rs.getString(8);//3. Kota kab
                objData[row][column++] = rs.getString(7);//4. Kecamatan
                objData[row][column++] = rs.getString(6);//5. Kelurahan
                objData[row][column++] = rs.getString(1);//6. NOP
                objData[row][column++] = rs.getString(2);//7. Nama WP
                objData[row][column++] = rs.getString(47);//8. Alamat OP 
                objData[row][column++] = rs.getString(3);//9. Alamat Wp
                objData[row][column++] = rs.getString(29);//10. Pajak Terhutang
                objData[row][column++] = rs.getString(26);//11. Tgl Terbit   
                objData[row][column++] = rs.getString(17);//12. Luas Bangunan   
                objData[row][column++] = rs.getString(16);//13. Luas Tanah   
                column = 0;
                row++;
            }
            if(conn!=null){
                conn.close();
            }
        }
        catch(SQLException e){
            System.err.println(e);
        }
        finally{
            return objData;
        }
    }
    public Object[] getPrinter(){
        Object[] dataPrinter = null;
        try{
            int rowcount;
            Connection conn = dbCon();

            Statement s;
            s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);

            ResultSet rs;

            rs = s.executeQuery("select * from pbb.pbb_sppt_printer");        

            rs.last();
            rowcount = rs.getRow();
            rs.beforeFirst();

            dataPrinter = new Object[rowcount+1];
            dataPrinter[0] = null;
            rowcount = 1;

            while(rs.next()){
                dataPrinter[rowcount] = "\\\\"+rs.getString(1)+"\\"+rs.getString(2);
                rowcount++;
            }
        
            if(conn!=null){
                conn.close();
            }
        }
        catch(SQLException e){
            System.err.println(e);
        }
        finally{
            return dataPrinter;
        }
    } 
    public Object getQuery(String query){
        Object dataQuery = null;
        try{
            Connection conn = dbCon();

            Statement s;
            s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

            ResultSet rs;        

            rs = s.executeQuery(query);                
            rs.next();
            dataQuery = rs.getObject(1);

            if(conn!=null){
                conn.close();
            }
        }
        catch(SQLException e){
            System.err.println(e);
        }
        finally{
            return dataQuery;
        }
    }
}
